﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using DreamBuilder.UI.UserControls.DataAccess.Services;
using System.Data;
using System.Data.SqlClient;
using DreamBuilder.Core.DAO;
using DreamBuilder.DictionaryAccess;


namespace DreamBuilder.UI.UserControls.DataAccess.Components
{
    public class MsSqlSPageListDAL : SPageListDAL
    {
        #region
        //CREATE PROCEDURE [dbo].[spSelectListDynamicPaged2]  
        //(  
        // @SelectStatement nvarchar(4000),  
        // @FromStatement nvarchar(2000),  
        // @WhereStatement nvarchar(4000),  
        // @OrderByExpression nvarchar(500),   
        // @AscOrDesc nvarchar(10),  
        // @RecordCount  int output ,  
        // @PageSize int,
        // @PageIndex int
        //)  

        //AS  

        //SET NOCOUNT ON  

        //DECLARE @RecordCountSQL NVARCHAR(4000)

        //SET @RecordCountSQL = 'SELECT @a = count(*)  FROM '+@FromStatement+' WHERE 1=1 '+@WhereStatement

        //EXEC sp_executesql  @RecordCountSQL,N'@a int output' ,@RecordCount output

        //if isnull(@WhereStatement,'') = '' 
        //begin
        //set @WhereStatement = '1=1'
        //end
        //else
        //begin
        //set @WhereStatement = right(ltrim(@WhereStatement),len(ltrim(@WhereStatement))-3)
        //end
        //if isnull(@OrderByExpression,'') = ''
        //begin
        //set @OrderByExpression = '1'
        //end
        //set @SelectStatement =  'select top 100000000 ' + @SelectStatement
        //declare @sql1 nvarchar(4000)
        //declare @sql2 nvarchar(4000)
        //set @sql1=N'select a.* , identity(int,1,1) as NumberIndex into #tempTablePage1 from (' + @SelectStatement + ' from ' + @FromStatement 
        //set @sql2 =N' where ' + @WhereStatement + ' order by ' + @OrderByExpression + ' ' + @AscOrDesc +') as a'
        //    +N' declare @nCount int'
        //    +N' declare @nTotalPage int'
        //    +N' declare @PageSize int set @PageSize='+rtrim(@PageSize)
        //    +N' declare @PageIndex int set @PageIndex='+rtrim(@PageIndex)
        //    +N' set @nCount = (select max(NumberIndex) from #tempTablePage1)'
        //    +N' if @nCount % @PageSize > 0 '
        //    +N' set @nTotalPage = @nCount / @PageSize + 1'
        //    +N' else'
        //    +N' set @nTotalPage = @nCount / @PageSize'
        //    +N' if @PageIndex <= 0 '
        //    +N' set @PageIndex = 1'
        //    +N' else if @PageIndex > @nTotalPage'
        //    +N' set @PageIndex = @nTotalPage'
        //    +N' if @PageIndex < @nTotalPage'
        //    +N' select * from #tempTablePage1 where NumberIndex between (@PageIndex - 1)*@PageSize + 1 and @PageIndex*@PageSize'
        //    +N' else if @PageIndex = @nTotalPage'
        //    +N' select * from #tempTablePage1 where NumberIndex between (@PageIndex-1)*@PageSize + 1 and @nCount '

        //exec(@sql1+@sql2)



        //GO

        #endregion

        public override System.Data.DataTable SelectListPaged(string select, string from, string where, string orderBy, string ascOrDesc, int intPageIndex, int intPageSize, out int recordCount, Dictionary<string, string> dictionary)
        {
            DbParameter p_recordCount = new SqlParameter("RecordCount", SqlDbType.Int);
            p_recordCount.Direction = ParameterDirection.Output;
            //SqlParameter p_recordCount = new SqlParameter { ParameterName = "@RecordCount", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output };
            DbParameter[] parameters = new DbParameter[]
            {
                ///*
                // * 尹东哲  2012/2/22
                // * 更改原因： 
                // *  1.因参数未写长度， 报字符串截取错误
                // *  2.DB.ParameterType 中 未提供在nvarchar 类型 固改写为SqlParameter
                // */
                //new SqlParameter{ ParameterName="@SelectStatement",SqlDbType = SqlDbType.NVarChar, Size=4000, Value=select },
                //new SqlParameter{ ParameterName="@FromStatement",SqlDbType = SqlDbType.NVarChar, Size=2000 ,Value=from},
                //new SqlParameter{ ParameterName="@WhereStatement",SqlDbType = SqlDbType.NVarChar, Size=4000 , Value= where},
                //new SqlParameter{ ParameterName="@OrderByExpression",SqlDbType = SqlDbType.NVarChar, Size=500 , Value= orderBy},
                //new SqlParameter{ ParameterName="@AscOrDesc",SqlDbType = SqlDbType.NVarChar, Size=10 , Value=ascOrDesc},
                //new SqlParameter{ ParameterName="@PageSize",SqlDbType = SqlDbType.Int, Size=4, Value=intPageSize},
                //new SqlParameter{ ParameterName="@PageIndex",SqlDbType = SqlDbType.Int, Size=4 , Value= intPageIndex},
                //p_recordCount

                DB.CreateSqlParameter("SelectStatement",select, ParameterDirection.Input,DB.ParameterType.Varchar),
                DB.CreateSqlParameter("FromStatement",from, ParameterDirection.Input,DB.ParameterType.Varchar),
                DB.CreateSqlParameter("WhereStatement",where, ParameterDirection.Input,DB.ParameterType.Varchar),
                DB.CreateSqlParameter("OrderByExpression",orderBy, ParameterDirection.Input,DB.ParameterType.Varchar),
                DB.CreateSqlParameter("AscOrDesc",ascOrDesc, ParameterDirection.Input,DB.ParameterType.Varchar),
                DB.CreateSqlParameter("PageIndex",intPageIndex, ParameterDirection.Input,DB.ParameterType.Int),
                DB.CreateSqlParameter("PageSize",intPageSize, ParameterDirection.Input,DB.ParameterType.Int),
                p_recordCount
            };

            DataTable dtInfo = DBHelper.RunProcedureDataTable("spSelectListDynamicPaged2", parameters);
            recordCount = Convert.ToInt32(p_recordCount.Value);
            return dtInfo;
        }

        //add by guoyin
        /// <summary>
        /// 一个重载，不分页的查询数据
        /// </summary>
        /// <param name="select"></param>
        /// <param name="from"></param>
        /// <param name="where"></param>
        /// <param name="orderBy"></param>
        /// <param name="ascOrDesc"></param>
        /// <param name="recordCount"></param>
        /// <param name="dictionary"></param>
        /// <returns></returns>
        public System.Data.DataTable SelectListPaged(string select, string from, string where, string orderBy, string ascOrDesc, out int recordCount, Dictionary<string, string> dictionary)
        {
            string sql = " select " + select + " from " + from + " where 1 = 1 " + where + "order by" + orderBy + "";

            DataTable dtInfo = DBHelper.ExecuteDataSet(sql).Tables[0];
            recordCount = dtInfo.Rows.Count;
            return dtInfo;
        }


        public override void GenerateListSql(CVoucherListItemInfo pListItemInfo, ref string selectSql, ref string fromSql, ref int columnIndex, Dictionary<string, string> tempDictionary)
        {
            string sTableCode = pListItemInfo.TableCode.Trim();
            string sFieldCode = pListItemInfo.FieldCode.Trim();
            FieldDicInfo pFieldDicInfo = DictionaryAccessFacade.GetFieldDicInfo(sTableCode, sFieldCode);
            //如果此列为自增型的主键

            //这里如果是参照自身容易引发死循环问题
            if (pFieldDicInfo.FieldType == FIELDTYPE.TYPE_Reference)
            {
                selectSql += sTableCode + "." + sFieldCode + " as '" + sTableCode + "." + sFieldCode + "',";
                //获取显示值
                UCControlCommon.GetRefSelectAndFromSql(pFieldDicInfo, ref  fromSql, ref  selectSql);
            }
            else if (pFieldDicInfo.FieldType == FIELDTYPE.TYPE_Bool)
            {
                selectSql += "(case  " + sTableCode + "." + sFieldCode + " when 1 then '1' else '0' END) as '" + sTableCode + "." + sFieldCode + "',"; //selectSql += sTableCode + "." + sFieldCode + " AS '" + sTableCode + "." + sFieldCode + "',";
                selectSql += "(case  " + sTableCode + "." + sFieldCode + " when 1 then '" + Common.CResManager.GetPublicString("String", "Yes") + "' else '" + Common.CResManager.GetPublicString("String", "No") + "' end) as " + sTableCode + "__" + sFieldCode + ",";
            }
            else
            {
                selectSql += sTableCode + "." + sFieldCode + " as " + sTableCode + "__" + sFieldCode + ",";
            }
        }

        public override string GetRestrictFKField(string tableCode, string refTableCode)
        {
            string fkField = "";
            string sql = @"SELECT 
                         OwnKeyName=(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid)
                            FROM sysobjects a
                        join sysforeignkeys b on a.id=b.constid
                        join sysobjects c on a.parent_obj=c.id
                    where a.xtype='f' AND c.xtype='U' and object_name(b.rkeyid) = '";
            sql += refTableCode;
            sql += "' and object_name(b.fkeyid) =  '";
            sql += tableCode;
            sql += "'";
            IDataReader reader = DBHelper.ExecuteReader(sql);
            while (reader.Read())
            {
                fkField = reader["OwnKeyName"].ToString();
            }
            reader.Close();
            return fkField;
        }
    }
}
